USE master
GO
if exists (select name from sys.databases where name = 'CanteenManagementSystem')
	Drop Database CanteenManagementSystem
GO
CREATE DATABASE CanteenManagementSystem
GO
USE CanteenManagementSystem
GO
CREATE TABLE Departments(
DeptID int IDENTITY(1,1) PRIMARY KEY,
DeptName nvarchar(50) not null,
DeptStatus bit DEFAULT (1)
)
GO
CREATE TABLE SubDepartments(
SubDeptID int IDENTITY(1,1) PRIMARY KEY,
SubDeptName varchar(100) not null, 
DeptID int not null REFERENCES Departments(DeptID),
SubStatus bit not null DEFAULT (1)
)
GO
CREATE TABLE Employees(
EmployeeID int IDENTITY(1,1) PRIMARY KEY,
UserName varchar(30) not null,
[Password] varchar(100) not null,
EmployeeCode varchar(20) not null,
EmployeeName nvarchar(20) not null,
Gender varchar(5) not null,
[Address] nvarchar(300),
Birth datetime not null,
PhoneHome varchar(15),
Mobile varchar(15),
DeptID int not null REFERENCES Departments(DeptID),
SubDeptID int not null REFERENCES SubDepartments(SubDeptID),
EDoj datetime not null,
EType bit not null DEFAULT (0),
EStatus bit not null DEFAULT (1)
)
GO
CREATE TABLE ItemType(
ItemTypeID int IDENTITY(1,1) PRIMARY KEY,
ItemTypeName nvarchar(50) not null,
) 
GO
CREATE TABLE Items(
ItemID int IDENTITY(1,1) PRIMARY KEY,
ItemName nvarchar(50) not null,
ItemTypeID int not null REFERENCES ItemType(ItemTypeID),
Price float not null,
Quantity int not null,
ItemImg nvarchar(200) not null,
ItemStatus bit not null DEFAULT(1)
)
GO
CREATE TABLE Coupons(
CouponID int Identity(1,1) PRIMARY KEY,
Criteria float not null,
CPercent int not null
)
GO
CREATE TABLE Bills(
BillID int IDENTITY(1,1) PRIMARY KEY,
BillDate datetime not null,
DeptID int not null REFERENCES Departments(DeptID),
EmployeeID int not null REFERENCES Employees(EmployeeID), 
CouponID int not null REFERENCES Coupons(CouponID),
BillStatus bit not null DEFAULT(1)
)
GO
CREATE TABLE BillDetails(
BillDetailID int identity(1,1) PRIMARY KEY,
BillID int not null REFERENCES Bills(BillID),
ItemID int not null REFERENCES Items(ItemID),
ItemQuantiy int not null
)

GO

 CREATE PROC UpdateEmployee
@EmployeeID int,
@UserName varchar(30) ,
@Password varchar(100) ,
@EmployeeCode varchar(20),
@EmployeeName nvarchar(20),
@Gender varchar(5) ,
@Address nvarchar(300),
@Birth datetime,
@PhoneHome varchar(15),
@Mobile varchar(15),
@DeptID int ,
@SubDeptID int,
@EType bit,
@EStatus bit
 as
 UPDATE Employees SET UserName=@UserName,[Password]=@Password,EmployeeCode=@EmployeeCode,EmployeeName=@EmployeeName,Gender=@Gender,[Address]=@Address,Birth=@Birth,PhoneHome=@PhoneHome,Mobile=@Mobile,DeptID=@DeptID,SubDeptID=@SubDeptID,EType=@EType,EStatus=@EStatus WHERE EmployeeID=@EmployeeID;

GO
INSERT INTO ItemType VALUES('Food');
INSERT INTO ItemType VALUES('Drink');
GO
INSERT INTO Items VALUES('Beef',1,4,20,'build/classes/Image/Item/beef.png',1)
INSERT INTO Items VALUES('Hamburger',1,3,10,'build/classes/Image/Item/hamburger.png',1)
INSERT INTO Items VALUES('Hot Dog',1,3.5,16,'build/classes/Image/Item/hot_dog.png',1)
INSERT INTO Items VALUES('Noodles',1,5,30,'build/classes/Image/Item/noo.png',1)
INSERT INTO Items VALUES('Phomat',1,4,50,'build/classes/Image/Item/phomat.png',1)
INSERT INTO Items VALUES('Pizza',1,3,40,'build/classes/Image/Item/pizza.png',1)
INSERT INTO Items VALUES('Toast',1,2,35,'build/classes/Image/Item/toast.png',1);
GO
INSERT INTO Departments VALUES('informatics',1)
INSERT INTO Departments VALUES('economic',1)
GO
INSERT INTO SubDepartments VALUES('programmer',1,1)
GO
INSERT INTO Employees VALUES('nguyen','1234567','c1106kv','Dinh Trong Nguyen','Nam','Boi khe/Chuyen My/Phu Xuyen/Ha Noi','04/12/1993','012345678','012345678',1,1,'11/18/2012',1,1)
INSERT INTO Employees VALUES('sa','1234567','c1106kv','Dinh Trong Nguyen','Nam','Boi khe/Chuyen My/Phu Xuyen/Ha Noi','04/12/1993','012345678','012345678',1,1,'11/18/2012',0,1)

GO
INSERT INTO Coupons VALUES(5,30)
GO
INSERT INTO Bills VALUES('11/29/2012',1,1,1,1)
GO
INSERT INTO BillDetails VALUES(1,1,5)
INSERT INTO BillDetails VALUES(1,2,3)
INSERT INTO BillDetails VALUES(1,2,6)
INSERT INTO BillDetails VALUES(1,1,4)
INSERT INTO BillDetails VALUES(1,1,2)